import re
import openpyxl
from openpyxl.styles import Font, Alignment

def extract_strings_from_file(file_path):
    """从文本文件中提取所有双引号内的英文字符串并去重"""
    with open(file_path, 'r', encoding='utf-8') as file:
        content = file.read()
    
    # 使用正则表达式匹配所有双引号内的内容
    pattern = re.compile(r'"(.*?)"')
    matches = pattern.findall(content)
    
    # 过滤出英文字符串（包含英文字母的）并去重
    english_strings = set()  # 使用集合自动去重
    for s in matches:
        if any(char.isalpha() for char in s):  # 检查是否包含字母
            english_strings.add(s.strip())  # 去除前后空格
    
    # 转换回列表并按原始顺序排序（如果需要保持顺序）
    # 如果不需要保持顺序，可以直接返回 list(english_strings)
    unique_strings = []
    seen = set()
    for s in matches:
        stripped = s.strip()
        if any(char.isalpha() for char in stripped) and stripped not in seen:
            unique_strings.append(stripped)
            seen.add(stripped)
    
    return unique_strings  # 或者 return list(english_strings) 如果不需要保持顺序

def write_to_excel(strings, output_file):
    """将提取的字符串写入Excel表格"""
    # 创建工作簿和工作表
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "待翻译字符串"
    
    # 设置表头
    ws['A1'] = "ID"
    ws['B1'] = "原始字符串(英文)"
    ws['C1'] = "翻译后的字符串(中文)"
    ws['D1'] = "备注"
    
    # 设置表头格式
    for col in range(1, 5):
        ws.cell(row=1, column=col).font = Font(bold=True)
        ws.cell(row=1, column=col).alignment = Alignment(horizontal='center')
    
    # 写入数据
    for i, string in enumerate(strings, start=2):
        ws.cell(row=i, column=1, value=i-1)  # ID
        ws.cell(row=i, column=2, value=string)  # 原始字符串
        
        # 设置单元格格式
        ws.cell(row=i, column=2).alignment = Alignment(wrap_text=True)
    
    # 自动调整列宽
    for column in ws.columns:
        max_length = 0
        column_letter = column[0].column_letter
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2) * 1.2
        ws.column_dimensions[column_letter].width = adjusted_width
    
    # 保存Excel文件
    wb.save(output_file)
    print(f"成功将{len(strings)}个字符串写入到{output_file}")

if __name__ == "__main__":
    input_file = r"sourceData_EN.txt"  # 替换为你的输入文件路径
    output_file = r"translation_strings_EN.xlsx"  # 输出Excel文件路径
    
    # 提取字符串
    strings = extract_strings_from_file(input_file)
    
    # 写入Excel
    write_to_excel(strings, output_file)